﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using DevCore.Log;

namespace DevCore.DB
{
    //存储过程参数类

    /// <summary>
    /// 数据库sql或者存储过程参数信息
    /// </summary>
    public class DbParamInfo
    {
        /// <summary>
        /// 参数名称
        /// </summary>
        public String Name { get; set; }
        /// <summary>
        /// 参数值
        /// </summary>
        public Object Value { get; set; }
        /// <summary>
        /// 参数方向
        /// </summary>
        public int Direction { get; set; }
        /// <summary>
        /// 参数类型
        /// </summary>
        public String DbType { get; set; }
        /// <summary>
        /// 参数大小 
        /// </summary>
        public int Size { get; set; }
    }


    /// <summary>
    /// SqlServer 操作辅助类
    /// 1.可以指定系统默认配置的数据库连接，也可以使用特定的数据库连接字符串。
    /// 2.可以选择启用事务进行处理。
    /// </summary>
    /// 
    public class SqlHelper
    {
        private SqlConnection _connection;
        private SqlTransaction _transaction;
        private SqlDataReader _reader;
        private String _connectString = String.Empty;
        private Boolean _bUseTransaction = false;

        /// <summary>
        /// 默认构造函数
        /// </summary>
        public SqlHelper()
        {
            //默认事务是关闭的
            _bUseTransaction = false;
            _connectString = ConfigurationSettings.AppSettings["SqlConn"];
            this._connection = new SqlConnection(_connectString);
        }

        /// <summary>
        /// 重载构造函数, 是否启用事务
        /// </summary>
        /// <param name="bUseTransaction"></param>
        /// <param name="connectString"></param>
        public SqlHelper(Boolean bUseTransaction, String connectString)
        {
            _bUseTransaction = bUseTransaction;
            _connectString = connectString;
            this._connection = new SqlConnection(_connectString);
        }


        /// <summary>
        /// 是否启用事务，使用默认字符串
        /// </summary>
        /// <param name="bUseTransaction"></param>
        public SqlHelper(Boolean bUseTransaction)
        {
            _bUseTransaction = bUseTransaction;
            _connectString = ConfigurationSettings.AppSettings["SqlConn"];
            this._connection = new SqlConnection(_connectString);
        }

        /// <summary>
        /// 是否启用事务进行执行.
        /// </summary>
        /// <param name="bTrue"></param>
        public void SetTran(Boolean bTrue)
        {
            _bUseTransaction = bTrue;
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void Open()
        {
            try
            {
                this._connection.Open();
            }
            catch(Exception ex)
            {
                LogHelper.WriteLog(ex.Message);
            }
        }

        /// <summary>
        /// 关闭数据库
        /// </summary>
        public void Close()
        {
            try
            {
                if (this._connection.State != ConnectionState.Closed)
                {
                    this._connection.Close();
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex.Message);
            }
        }

        /// <summary>
        /// 开始事务
        /// </summary>
        public void BeginTransaction()
        {
            try
            {
                this._transaction = this._connection.BeginTransaction();
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex.Message);
            }
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTransaction()
        {
            try
            {
                this._transaction.Commit();
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex.Message);
            }
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollbackTransaction()
        {
            try
            {
                this._transaction.Rollback();
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex.Message);
            }
        }

        /// <summary>
        /// 执行Command命令
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(SqlCommand cmd)
        {
            int rValue = -1;
            try
            {
                rValue = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex.Message);
                throw;
            }
            return rValue;
        }

        /// <summary>
        /// 得到第一行第一列的值
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public object ExecuteScalar(SqlCommand cmd, string sql)
        {
            object rValue = null;
            try
            {
                rValue = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex.Message);
                throw;
            }
            return rValue;
        }

        /// <summary>
        /// 得到Reader对象
        /// </summary>
        /// <param name="cmd">Command命令</param>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public SqlDataReader GetReader(SqlCommand cmd, string sql)
        {
            try
            {
                this._reader = cmd.ExecuteReader();

            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex.Message);
            }
            return this._reader;
        }

        /// <summary>
        /// 得到DataSet对象
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public DataSet ExecuteDataset(SqlCommand cmd)
        {
            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex.Message);
                throw;
            }

        }



        /// <summary>
        /// 得到表
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(SqlCommand cmd)
        {
            DataTable dt = null;
            try
            {
                DataSet ds = ExecuteDataset(cmd);
                if (ds != null)
                {
                    dt = ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex.Message);
                throw;
            }

            return dt;
        }


        /// <summary>
        /// 关闭Reader
        /// </summary>
        public void CloseReader()
        {
            try
            {
                if (this._reader.IsClosed == false)
                {
                    this._reader.Close();
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex.Message);
            }
        }

        /// <summary>
        /// 创建SqlCommand对像，在这里决定是否采用事务处理
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public SqlCommand GetSqlDbCommand(string sql)
        {
            SqlCommand cmd = null; 
            // 判断是否启用事务
            try
            {
                if (_bUseTransaction)
                {
                    cmd = new SqlCommand(sql, this._connection, this._transaction);
                }
                else
                {
                    cmd = new SqlCommand(sql, this._connection);
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex.Message);
                //throw;
            }
 
            return cmd;

        }

        /// <summary>
        /// 根据存储过程来得到sqlCommand对像
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns></returns>
        public SqlCommand GetProcDbCommand(String procName)
        {
            SqlCommand cmd = null;
            try
            {
                cmd = _connection.CreateCommand();
                cmd.CommandText = procName;
                cmd.CommandType = CommandType.StoredProcedure;
                if (_bUseTransaction)
                {
                    cmd.Transaction = _transaction;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex.Message);
                //throw;
            }

            return cmd;
        }

        /// <summary>
        /// 根据传进的字符来判断当前的参数是什么类型的。
        /// </summary>
        /// <param name="dbTypeString"></param>
        /// <returns></returns>
        public SqlDbType GetByDbTypeString(String dbTypeString)
        {
            SqlDbType curDBType = new SqlDbType();
            dbTypeString = dbTypeString.ToLower();

            if (dbTypeString == "varchar")
            {
                curDBType = SqlDbType.VarChar;
            }

            if (dbTypeString == "nvarchar")
            {
                curDBType = SqlDbType.NVarChar;
            }

            if (dbTypeString == "text")
            {
                curDBType = SqlDbType.Text;
            }

            if (dbTypeString == "bigint")
            {
                curDBType = SqlDbType.BigInt;
            }
            if (dbTypeString == "float")
            {
                curDBType = SqlDbType.Float;
            }

            if (dbTypeString == "double")
            {
                curDBType = SqlDbType.Decimal;
            }

            if (dbTypeString == "money")
            {
                curDBType = SqlDbType.Money;
            }
            return curDBType;
        }

        #region cmd参数控制
        /// <summary>
        /// 传入参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="parameterName"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        /// <param name="parameterValue"></param>
        public void AddInProcParameter(SqlCommand cmd, String parameterName, String dbType, int size, object parameterValue)
        {
            DbParamInfo dbParamInfo = new DbParamInfo();
            dbParamInfo.DbType = dbType;
            dbParamInfo.Name = parameterName;
            dbParamInfo.Size = size;
            dbParamInfo.Direction = 0;
            AddProcParameter(cmd, dbParamInfo);
        }

        /// <summary>
        /// 输出参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="parameterName"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        public void AddOuProctParameter(SqlCommand cmd, String parameterName, String dbType, int size)
        {
            DbParamInfo dbParamInfo = new DbParamInfo();
            dbParamInfo.DbType = dbType;
            dbParamInfo.Name = parameterName;
            dbParamInfo.Size = size;
            dbParamInfo.Direction = 1;
            AddProcParameter(cmd, dbParamInfo);

        }

        /// <summary>
        /// 增加存储过程参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="dicParams"></param>
        public void AddProcParameter(SqlCommand cmd, DbParamInfo dbParamInfo)
        {
            SqlParameter parameter = new SqlParameter();
            parameter.SqlDbType = GetByDbTypeString(dbParamInfo.DbType);
            parameter.Size = dbParamInfo.Size;
            // 输入方向 
            if (dbParamInfo.Direction == 0)
            {
                parameter.ParameterName = "@pi_" + dbParamInfo.Name;
                parameter.Direction = ParameterDirection.Input;
            }

            //输出方向
            if (dbParamInfo.Direction == 1)
            {
                parameter.ParameterName = "@po_" + dbParamInfo.Name;
                parameter.Direction = ParameterDirection.Output;
            }
            parameter.Value = dbParamInfo.Value;
            cmd.Parameters.Add(parameter);
        }

        /// <summary>
        /// 增加存储过程参数集合
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="dicParams">存储过程参数集合</param>
        public void AddProcParameters(SqlCommand cmd, List<DbParamInfo> dicParams)
        {
            foreach (DbParamInfo dbParamInfo in dicParams)
            {
                AddProcParameter(cmd, dbParamInfo);
            }
        }

        /// <summary>
        /// 增加Sql参数至字符串
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="dicParams"></param>
        public void AddSqlParam(SqlCommand cmd, List<DbParamInfo> dicParams)
        {
            for (int i = 0; i < dicParams.Count; i++)
            {
                DbParamInfo curColumn = dicParams[i];
                cmd.Parameters.AddWithValue(curColumn.Name, curColumn.Value);
            }
        }

        #endregion



    }
}